package DBLayer;

import java.sql.*;
import java.util.ArrayList;

import DBLayer.DBConnection1;
import DBLayer.IFDBProductLine;
import ModelLayer.Product;
import ModelLayer.ProductLine;
import ModelLayer.SalesOrder;

public class DBProductLine implements IFDBProductLine{
	private Connection con;
	
	public DBProductLine()
	{
		 con = DBConnection1.getInstance().getDBcon();
	}
	
//implemented method from the interface	
	@Override
	public ProductLine getProductLineByID(int productId, int orderId, boolean retrieveAssociation)
	{
		ProductLine productLine = new ProductLine();
		productLine = singleWhere("productId='"+productId+"'"+" AND orderId='"+orderId+"'", retrieveAssociation);
		return productLine;
	}

	@Override
	public ArrayList<ProductLine> getProductLinesForOrder(int orderId, boolean retrieveAssociation)
	{
		return miscWhere("orderId="+orderId, retrieveAssociation);
	}
	
	//insert
	public int insertProductLine(ProductLine productLine) throws Exception
	{
		String query = "INSERT INTO ProductLine(productId, quantity, price, orderId) VALUES('" +
				productLine.getProduct().getId()+"', '"+
				productLine.getQuantity() +"', '"+
				productLine.getSubPrice()+"', '"+
				productLine.getOrder().getId() + "')";
		
		int rc = -1;
		System.out.println("insert: " + query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		} catch (SQLException ex) {
			System.out.println("ProductLine is not inserted");
	        throw new Exception ("ProductLine is not inserted correct");
		}
		
		return rc;
	}
	
	//update
	public int updateProductLine(ProductLine productLine) {
		ProductLine pl=productLine;
		int rc = -1;
		
		String query = "UPDATE ProductLine SET " +
				"quantity='"+pl.getQuantity()+"', "+
				"price='"+pl.getSubPrice()+"' "+
						"WHERE orderId=" + pl.getOrder().getId()+" AND productId="+pl.getProduct().getId();
		
		System.out.println("Update query: " + query);
		
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc=stmt.executeUpdate(query);
			stmt.close();
		} catch (Exception e) {
			System.out.println("Update exception in SalesOrder: " + e);
		}
		
		return rc;
	}
	
	//delete
	public int deleteProductLine(int productId, int orderId) {
		int rc=-1;
		  
	  	String query="DELETE FROM ProductLine WHERE orderId = " +
				orderId + " AND productId="+productId;
                System.out.println(query);
	  	try{ 
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in salesorder db: "+ex);
   	        }
		return(rc);
	}	
	
//private methods
    //michWere is used whenever we want to select more than one employee
	private ArrayList<ProductLine> miscWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList<ProductLine> list = new ArrayList<>();
		
		String query=buildQuery(wClause);
		
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			while(results.next())
			{
				ProductLine productLine = new ProductLine();
				productLine = buildProductLine(results);
				list.add(productLine);
			}
			stmt.close();
			if(retrieveAssociation)
			{
				for(ProductLine productLine : list)
				{
					//Make association with the Product
					IFDBProduct dbproductObj = new DBProduct();
					Product productObj = dbproductObj.getProductByID(productLine.getProduct().getId(), true);
					System.out.println("Product is found!");
					productLine.setProduct(productObj);
					//Make association with the Order
					IFDBSalesOrder dbsalesOrderObj = new DBSalesOrder();
					SalesOrder salesOrderObj = dbsalesOrderObj.getSalesOrdersByID(productLine.getOrder().getId(), false);
					System.out.println("Order is found!");
					productLine.setOrder(salesOrderObj);
				}
			}
		} catch (Exception e) {
			System.out.println("Query exception - select: "+e);
			e.printStackTrace();
		}
		
		return list;
	}
	
	//SingleWhere is used when we only select one employee 	
	private ProductLine singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ProductLine productLine = new ProductLine();
		
		String query = buildQuery(wClause);
		System.out.println(query);
		
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			if(results.next())
			{
				productLine=buildProductLine(results);
				stmt.close();
				if (retrieveAssociation) {
					//Make association with the Product
					IFDBProduct dbproductObj = new DBProduct();
					Product productObj = dbproductObj.getProductByID(productLine.getProduct().getId(), true);
					System.out.println("Product is found!");
					productLine.setProduct(productObj);
					//Make association with the Order
					IFDBSalesOrder dbsalesOrderObj = new DBSalesOrder();
					SalesOrder salesOrderObj = dbsalesOrderObj.getSalesOrdersByID(productLine.getOrder().getId(), false); 
					System.out.println("Order is found!");
					productLine.setOrder(salesOrderObj);
				}
			}
		} catch (Exception e) {
			System.out.println("Query exception: "+e);
		}
		
		return productLine;
	}
	
	//method to build the query
	private String buildQuery(String wClause)
	{
		String query = "SELECT productId, quantity, price, orderId FROM ProductLine";
		if (wClause.length()>0) {
			query = query + " WHERE " + wClause;
		}
		
		return query;
	}
	
	//method to build the object
	private ProductLine buildProductLine(ResultSet results)
	{
		ProductLine productLine = new ProductLine();
		try {
			productLine.setProduct(new Product(results.getInt("productId")));
			productLine.setQuantity(results.getInt("quantity"));
			productLine.setOrder(new SalesOrder(results.getInt("orderId")));
		} catch (Exception e) {
			System.out.println("error in building the ProductLine object");
		}
		return productLine;
	}
}
